<?php
$current_datetime = date('Y-m-d H:i:s');
$current_date = date('Y-m-d');


$sqls = array(
    // create table admin_user
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'admin_user";',
    'CREATE TABLE "public"."'.$user_site_prefix.'admin_user" (
        "id" serial NOT NULL,
        "username" varchar(64) NOT NULL,
        "password" varchar(32) NOT NULL,
        "email" varchar(64),
        "created_date" date,
        "last_login" date,
        "validation_code" varchar(64),
        "validation_type" int2,
        "validation_expired" int2,
        "status" int2
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table admin_user
    'ALTER TABLE public.'.$user_site_prefix.'admin_user ADD PRIMARY KEY ("id");',
    // insert into table admin_user
    "INSERT INTO public.".$user_site_prefix."admin_user (username,password,email,created_date,status) VALUES ('".$this->site->user->username."','".$this->site->user->password."','".$this->site->user->email."','".$current_date."','1')",
    
    // create table user
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'user";',
    'CREATE TABLE "public"."'.$user_site_prefix.'user" (
        "id" serial NOT NULL,
        "username" varchar(64) NOT NULL,
        "password" varchar(32) NOT NULL,
        "email" varchar(64),
        "created_date" date,
        "last_login" date,
        "validation_code" varchar(64),
        "validation_type" int2,
        "validation_expired" int2,
        "status" int2
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table user
    'ALTER TABLE public.'.$user_site_prefix.'user ADD PRIMARY KEY ("id");',
    // insert into table user
    "INSERT INTO public.".$user_site_prefix."user (username, password, email, created_date, status) VALUES ('".$this->site->user->username."','".$this->site->user->password."','".$this->site->user->email."','".$current_date."','1')",
    
    // create table authitemchild
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'authitemchild";',
    'CREATE TABLE "public"."'.$user_site_prefix.'authitemchild" (
        "parent" varchar(64) NOT NULL,
        "child" varchar(64) NOT NULL
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table authitemchild
    'ALTER TABLE public.'.$user_site_prefix.'authitemchild ADD PRIMARY KEY ("parent","child");',
    
    // create table authassignment
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'authassignment";',
    'CREATE TABLE "public"."'.$user_site_prefix.'authassignment" (
        "itemname" varchar(64) NOT NULL,
        "userid" int4 NOT NULL,
        "bizrule" text,
        "data" text
    )
    WITH (OIDS=FALSE);',
    // add primary key for table authassignment
    'ALTER TABLE public.'.$user_site_prefix.'authassignment ADD PRIMARY KEY ("itemname","userid");',
    // insert into table authassignment
    "INSERT INTO public.".$user_site_prefix."authassignment (itemname,userid,bizrule,data) VALUES ('administrators', '1', null, null);",
    
    // create table authitem
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'authitem";',
    'CREATE TABLE "public"."'.$user_site_prefix.'authitem" (
        "name" varchar(64) NOT NULL,
        "type" int4 NOT NULL,
        "description" text,
        "bizrule" text,
        "data" text
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table authitem
    'ALTER TABLE public.'.$user_site_prefix.'authitem ADD PRIMARY KEY ("name");',
    // insert into table authitem
    'INSERT INTO public.'.$user_site_prefix.'authitem VALUES (\'administrators\', \'2\', \'Super administrator\', null, \'N;\');',
    
    // foreign key for table authassignment & authitem
    'ALTER TABLE public.'.$user_site_prefix.'authassignment ADD FOREIGN KEY ("itemname") REFERENCES public.'.$user_site_prefix.'authitem ("name") ON DELETE CASCADE ON UPDATE CASCADE;',
    // foreign key for table authitemchild & authitem
    'ALTER TABLE public.'.$user_site_prefix.'authitemchild ADD FOREIGN KEY ("parent") REFERENCES public.'.$user_site_prefix.'authitem ("name") ON DELETE CASCADE ON UPDATE CASCADE;',
    'ALTER TABLE public.'.$user_site_prefix.'authitemchild ADD FOREIGN KEY ("child") REFERENCES public.'.$user_site_prefix.'authitem ("name") ON DELETE CASCADE ON UPDATE CASCADE;',
    
    // create table category
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'category";',
    'CREATE TABLE "public"."'.$user_site_prefix.'category" (
        "id" serial NOT NULL,
        "title" varchar(255),
        "alias" varchar(255),
        "description" text,
        "image" varchar(255),
        "parent_id" int4 DEFAULT 0,
        "is_active" int2,
        "ordering" int4
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table category
    'ALTER TABLE public.'.$user_site_prefix.'category ADD PRIMARY KEY ("id");',
    
    // create table content_context
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'content_context";',
    'CREATE TABLE "public"."'.$user_site_prefix.'content_context" (
        "id" serial NOT NULL,
        "content_type_id" int4 NOT NULL,
        "type" varchar(8) DEFAULT \'edit\'::character varying NOT NULL,
        "route" varchar(255) NOT NULL,
        "params" text,
        "is_frontend" bool DEFAULT false NOT NULL,
        "is_default" bool DEFAULT false NOT NULL,
        "roles" varchar(255),
        "status" bool DEFAULT true NOT NULL,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6),
        "context" varchar(512)
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table content_context
    'ALTER TABLE public.'.$user_site_prefix.'content_context ADD PRIMARY KEY ("id");',
    
    // create table content_type
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'content_type";',
    'CREATE TABLE "public"."'.$user_site_prefix.'content_type" (
        "id" serial NOT NULL,
        "name" varchar(128) NOT NULL,
        "description" text,
        "model" varchar(255),
        "criteria" text,
        "status" bool DEFAULT true,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6)
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table content_type
    'ALTER TABLE public.'.$user_site_prefix.'content_type ADD PRIMARY KEY ("id");',
    // insert into table content_type
    "INSERT INTO public.".$user_site_prefix."content_type (name,description,model,criteria,status,creation_datetime,last_update) VALUES ('Text', 'Simple text content', 'Cms.models.Text', null, 't', '2011-11-06 22:48:18', '2011-11-06 22:48:20');",
    "INSERT INTO public.".$user_site_prefix."content_type (name,description,model,criteria,status,creation_datetime,last_update) VALUES ('Image', 'Image contnet', 'Cms.models.Image', null, 't', '2011-11-06 22:48:52', '2011-11-06 22:48:54');",
    "INSERT INTO public.".$user_site_prefix."content_type (name,description,model,criteria,status,creation_datetime,last_update) VALUES ('Video', 'Video content', 'Cms.models.Video', null, 't', '2011-11-06 22:50:05', '2011-11-06 22:50:09');",
    
    // create table extension
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'extension";',
    'CREATE TABLE "public"."'.$user_site_prefix.'extension" (
        "id" serial NOT NULL,
        "event" varchar(255) NOT NULL,
        "class" varchar(64) NOT NULL,
        "method" varchar(64) NOT NULL,
        "config" text,
        "enabled" int2 NOT NULL
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table extension
    'ALTER TABLE public.'.$user_site_prefix.'extension ADD PRIMARY KEY ("id");',
    // create index for table extension
    'CREATE INDEX "extclididx_copy_'.$user_site_prefix.'" ON public.'.$user_site_prefix.'extension USING btree ("event", "enabled");',
    
    // create table image
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'image";',
    'CREATE TABLE "public"."'.$user_site_prefix.'image" (
        "id" serial NOT NULL,
        "source" varchar(512) NOT NULL,
        "alt" varchar(512),
        "status" bool DEFAULT true NOT NULL,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6)
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table image
    'ALTER TABLE public.'.$user_site_prefix.'image ADD PRIMARY KEY ("id");',
    
    // create table lookup
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'lookup";',
    'CREATE TABLE "public"."'.$user_site_prefix.'lookup" (
        "id" serial NOT NULL,
        "name" varchar(128) NOT NULL,
        "code" int8 NOT NULL,
        "type" varchar(128) NOT NULL,
        "position" int4
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table lookup
    'ALTER TABLE public.'.$user_site_prefix.'lookup ADD PRIMARY KEY ("id");',
    
    // create table module
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'module";',
    'CREATE TABLE "public"."'.$user_site_prefix.'module" (
        "id" serial NOT NULL,
        "name" varchar(64) NOT NULL,
        "friendly_name" varchar(255),
        "description" text,
        "version" varchar(64),
        "has_back_end" char(1) DEFAULT \'y\'::bpchar NOT NULL,
        "ordering" int4,
        "icon" varchar(255),
        "enabled" bool DEFAULT true NOT NULL,
        "is_system" bool DEFAULT false NOT NULL
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table module
    'ALTER TABLE public.'.$user_site_prefix.'module ADD PRIMARY KEY ("id");',
    
    // create table page
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'page";',
    'CREATE TABLE "public"."'.$user_site_prefix.'page" (
        "id" serial NOT NULL,
        "revision_id" int8,
        "site_id" int8 NOT NULL,
        "page_data" text,
        "revised_by" int8,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6),
        "title" varchar(255),
        "alias" varchar(255),
        "status" bool DEFAULT true NOT NULL,
        "parent_id" int8 DEFAULT 0,
        "ordering" int4 DEFAULT 0,
        "include_in_sitemap" bool DEFAULT true,
        "allow_cache" bool DEFAULT false,
        "type" varchar(32) DEFAULT \'cms\'::character varying,
        "route" varchar(512),
        "view" varchar(128)
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table page
    'ALTER TABLE public.'.$user_site_prefix.'page ADD PRIMARY KEY ("id");',
    
    // create table page_current_revision
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'page_current_revision";',
    'CREATE TABLE "public"."'.$user_site_prefix.'page_current_revision" (
        "page_id" int8 NOT NULL,
        "revision" int8 NOT NULL,
        "workflow_id" int8 NOT NULL
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table page_current_revision
    'ALTER TABLE public.'.$user_site_prefix.'page_current_revision ADD PRIMARY KEY ("page_id", "revision", "workflow_id");',

    // create table page_revision
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'page_revision";',
    'CREATE TABLE "public"."'.$user_site_prefix.'page_revision" (
        "id" int8 DEFAULT 1 NOT NULL,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6),
        "created_by" int8,
        "updated_by" int8,
        "log" text,
        "page_id" int8 NOT NULL,
        "image" varchar(255),
        "template_content_id" int8 DEFAULT 0,
        "js_files" text,
        "css_files" text
        )
        WITH (OIDS=FALSE);
    ',
    // create index for table page_revision
    'CREATE INDEX "pagerevpididx_copy_'.$user_site_prefix.'" ON public.'.$user_site_prefix.'page_revision USING btree ("page_id");',
    // add primary key for table page_revision
    'ALTER TABLE public.'.$user_site_prefix.'page_revision ADD PRIMARY KEY ("id", "page_id");',
    
    // create table page_url
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'page_url";',
    'CREATE TABLE "public"."'.$user_site_prefix.'page_url" (
        "id" serial NOT NULL,
        "page_id" int8 NOT NULL,
        "url" varchar(255) NOT NULL,
        "status" bool,
        "default" bool,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6)
        )
        WITH (OIDS=FALSE);
    ',
    // create index for table page_url
    'CREATE INDEX "pageurlidx_copy_'.$user_site_prefix.'" ON public.'.$user_site_prefix.'page_url USING btree ("page_id");',
    // add primary key for table page_url
    'ALTER TABLE public.'.$user_site_prefix.'page_url ADD PRIMARY KEY ("id");',
    
    // create table page_widget
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'page_widget";',
    'CREATE TABLE "public"."'.$user_site_prefix.'page_widget" (
        "id" serial NOT NULL,
        "page_id" int8 NOT NULL,
        "content_type_id" int8 NOT NULL,
        "widget_id" int8 NOT NULL,
        "widget_settings" text,
        "revision_id" int2 DEFAULT 1,
        "container" varchar(100),
        "ordering" int2,
        "status" bool,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6)
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table page_widget
    'ALTER TABLE public.'.$user_site_prefix.'page_widget ADD PRIMARY KEY ("id");',
    
    // create table setting
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'setting";',
    'CREATE TABLE "public"."'.$user_site_prefix.'setting" (
        "id" serial NOT NULL,
        "name" varchar(64) NOT NULL,
        "label" varchar(64),
        "value" text NOT NULL,
        "description" text,
        "setting_group" varchar(128),
        "ordering" int4,
        "visible" int2,
        "module" varchar(64)
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table setting
    'ALTER TABLE public.'.$user_site_prefix.'setting ADD PRIMARY KEY ("id");',
    // insert into table setting
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('ADMIN_EMAIL', 'Administrator''s email', '".$this->site->user->email."', 'Administrator email', '1. General settings', '5', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('TIMEZONE', 'Timezone', 'Asia/Bangkok', '', '', '9', '0', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('BO_PAGE_SIZE', 'Entries per page in Admin panel', '50', 'Number of entries per page in Back Office', '2. Appearance', '1', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('BO_THEME', 'Back Office theme', 'Admin', 'Back Office theme', '', '7', '0', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('DEFAULT_BO_LAYOUT', 'Default BO layout', 'main', 'Default Back Office layout', '', '8', '0', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('DEFAULT_LAYOUT', 'Default layout', '//layouts/main', 'Default layout', '', '6', '0', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('DEFAULT_META_DESCRIPTION', 'Default meta description', 'My website', null, '1. General settings', '2', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('DEFAULT_META_KEYWORDS', 'Default meta keywords', '', null, '1. General settings', '3', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('DEFAULT_PAGE_ID', 'Default page', '1', 'Default frontend page ID', '', '5', '0', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('MAIL_METHOD', 'Mail sending method', 'smtp', 'Method to send mails', '3. Email', '1', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('MAIL_SENDER_NAME', 'Email sender name', 'Serta.com', 'Email sender name', '3. Email', '7', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('MAIL_SERDER_ADDRESS', 'Email sender address', 'your@email.com', 'Email sender address', '3. Email', '8', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('MAIL_SIGNATURE', 'Email signature', 'Serta.com', 'Email signature', '3. Email', '9', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('PAGE_SIZE', 'Entries per page', '20', 'Number of entry per page', '2. Appearance', '2', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('SITE_COPYRIGHT', 'Copyright', '&copy; Serta International', 'Copyright text on footer', '1. General settings', '4', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('SITE_NAME', 'Site name', '".$this->site->site_name."', 'Site name, displayed on browser''s title and used for SEO', '1. General settings', '1', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('SITE_SECRET_KEY', 'Site secret key', 'eae60a4ee7e4000b08269c7ea7202d2b', 'Site secret key', '', '1', '0', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('SMTP_HOST', 'SMTP host', 'localhost', 'SMTP host name', '3. Email', '2', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('SMTP_PASSWORD', 'SMTP password', 'asdf', 'SMTP password', '3. Email', '6', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('SMTP_PORT', 'SMTP port', '25', 'SMTP port', '3. Email', '3', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('SMTP_SECURE', 'SMTP sercure connection', 'no', 'SMTP secure connection', '3. Email', '5', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('SMTP_USERNAME', 'SMTP username', 'stuart', 'SMTP username', '3. Email', '4', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('THEME', 'Theme', '".(($this->site->template!='') ? $this->site->template : 'blank')."', 'Frontend theme', '', '3', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('UPLOAD_FOLDER', 'User upload folder', 'uploads', 'User uploaded folder (you must grant write permission on this folder)', '1. General settings', '9', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('URL_EXT', 'URL extension', '.html', 'Url extension', '', '4', '1', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('TIME_FORMAT', 'Time format', 'h:mm a', '', '', '10', '0', '');",
    "INSERT INTO public.".$user_site_prefix."setting (name,label,value,description,setting_group,ordering,visible,module) VALUES ('CACHE_EXPIRE', 'CACHE_EXPIRE', '900', '', '1. General settings', '11', '1', '');",

    // create table taxonomy_index
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'taxonomy_index";',
    'CREATE TABLE "public"."'.$user_site_prefix.'taxonomy_index" (
        "object_id" int8 NOT NULL,
        "term_id" int8 NOT NULL,
        "ordering" int8 DEFAULT 0
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table taxonomy_index
    'ALTER TABLE public.'.$user_site_prefix.'taxonomy_index ADD PRIMARY KEY ("object_id", "term_id");',
    
    // create table taxonomy_term
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'taxonomy_term";',
    'CREATE TABLE "public"."'.$user_site_prefix.'taxonomy_term" (
        "id" serial NOT NULL,
        "v_id" int8 NOT NULL,
        "name" varchar(255) NOT NULL,
        "alias" varchar(255) NOT NULL,
        "description" text,
        "ordering" int8,
        "state" int2 DEFAULT 0
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table taxonomy_term
    'ALTER TABLE public.'.$user_site_prefix.'taxonomy_term ADD PRIMARY KEY ("id");',
    
    // create table taxonomy_term_hierarchy
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'taxonomy_term_hierarchy";',
    'CREATE TABLE "public"."'.$user_site_prefix.'taxonomy_term_hierarchy" (
        "term_id" int8 NOT NULL,
        "parent_id" int8 NOT NULL
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table taxonomy_term_hierarchy
    'ALTER TABLE public.'.$user_site_prefix.'taxonomy_term_hierarchy ADD PRIMARY KEY ("term_id", "parent_id");',

    // create table taxonomy_vocabulary
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'taxonomy_vocabulary";',
    'CREATE TABLE "public"."'.$user_site_prefix.'taxonomy_vocabulary" (
        "id" serial NOT NULL,
        "name" varchar(255) NOT NULL,
        "alias" varchar(255) NOT NULL,
        "description" text,
        "module" varchar(255) NOT NULL,
        "ordering" int8,
        "state" int2 DEFAULT 0,
        "properties_form_id" int8
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table taxonomy_vocabulary
    'ALTER TABLE public.'.$user_site_prefix.'taxonomy_vocabulary ADD PRIMARY KEY ("id");',

    // create table template
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'template";',
    'CREATE TABLE "public"."'.$user_site_prefix.'template" (
        "id" serial NOT NULL,
        "name" varchar(100) NOT NULL,
        "group" varchar(100),
        "status" bool,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6),
        "revision_id" int8 DEFAULT 1,
        "alias" varchar(128)
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table template
    'ALTER TABLE public.'.$user_site_prefix.'template ADD PRIMARY KEY ("id");',

    // create table template_content
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'template_content";',
    'CREATE TABLE "public"."'.$user_site_prefix.'template_content" (
        "id" serial NOT NULL,
        "template_id" int8,
        "revision_id" int8,
        "html" text NOT NULL,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6),
        "status" bool,
        "css_files" text,
        "js_files" text,
        "log" text,
        "parent_id" int8 NOT NULL
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table template_content
    'ALTER TABLE public.'.$user_site_prefix.'template_content ADD PRIMARY KEY ("id");',

    // create table template_site
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'template_site";',
    'CREATE TABLE "public"."'.$user_site_prefix.'template_site" (
        "template_id" int8 NOT NULL,
        "site_id" int8 NOT NULL
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table template_site
    'ALTER TABLE public.'.$user_site_prefix.'template_site ADD PRIMARY KEY ("template_id","site_id");',

    // create table text
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'text";',
    'CREATE TABLE "public"."'.$user_site_prefix.'text" (
        "id" serial NOT NULL,
        "content" text NOT NULL,
        "status" bool DEFAULT true NOT NULL,
        "mime" varchar(64) DEFAULT \'text/html\'::character varying,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6),
        "css" text
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table text
    'ALTER TABLE public.'.$user_site_prefix.'text ADD PRIMARY KEY ("id");',
    
    // create table video
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'video";',
    'CREATE TABLE "public"."'.$user_site_prefix.'video" (
        "id" serial NOT NULL,
        "source" varchar(512) NOT NULL,
        "type" int2 NOT NULL,
        "status" bool DEFAULT true NOT NULL,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6)
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table video
    'ALTER TABLE public.'.$user_site_prefix.'video ADD PRIMARY KEY ("id");',
    
    // create table widget
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'widget";',
    'CREATE TABLE "public"."'.$user_site_prefix.'widget" (
        "id" serial NOT NULL,
        "name" varchar(255) NOT NULL,
        "description" text,
        "images" text,
        "video" varchar(512),
        "path" varchar(512) NOT NULL,
        "author" varchar(512),
        "version" varchar(128),
        "status" bool DEFAULT true NOT NULL,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6)
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table widget
    'ALTER TABLE public.'.$user_site_prefix.'widget ADD PRIMARY KEY ("id");',
    // insert into table widget
    "INSERT INTO public.".$user_site_prefix."widget (name,description,images,video,path,author,version,status,creation_datetime) VALUES ('PureHTML', 'Display a piece of HTML content as it is', null, null, 'Cms.components.widgets.PureHtml', 'Hudson Nguyen', '1', 't', '".$current_datetime."');",
    "INSERT INTO public.".$user_site_prefix."widget (name,description,images,video,path,author,version,status,creation_datetime) VALUES ('Image', 'Display an image', null, null, 'Cms.components.widgets.CmsImage', 'Hudson Nguyen', '1', 't', '".$current_datetime."');",
    "INSERT INTO public.".$user_site_prefix."widget (name,description,images,video,path,author,version,status,creation_datetime) VALUES ('GenericView', 'Display data of a content item', null, null, 'Cms.components.widgets.GenericView', 'Hudson Nguyen', '1', 't', '".$current_datetime."');",
    "INSERT INTO public.".$user_site_prefix."widget (name,description,images,video,path,author,version,status,creation_datetime) VALUES ('GenericList', 'Show a list of content items', null, null, 'Cms.components.widgets.GenericList', 'Hudson Nguyen', '1', 't', '".$current_datetime."');",
    "INSERT INTO public.".$user_site_prefix."widget (name,description,images,video,path,author,version,status,creation_datetime) VALUES ('GenericForm', 'Display a form for your visitor to submit data', null, null, 'Cms.components.widgets.GenericForm', 'Hudson Nguyen', '1', 't', '".$current_datetime."');",

    // create table widget_layout
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'widget_layout";',
    'CREATE TABLE "public"."'.$user_site_prefix.'widget_layout" (
        "id" serial NOT NULL,
        "name" varchar(255) NOT NULL,
        "description" text NOT NULL,
        "images" text,
        "path" varchar(512) NOT NULL,
        "author" varchar(512),
        "version" varchar(128),
        "status" bool,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6),
        "widget_id" int4 NOT NULL
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table widget_layout
    'ALTER TABLE public.'.$user_site_prefix.'widget_layout ADD PRIMARY KEY ("id");',
    // insert into table widget_layout
    "INSERT INTO public.".$user_site_prefix."widget_layout (name,description,images,path,author,version,status,creation_datetime,last_update,widget_id) VALUES ('Default', 'List content items of a content type with pagination', null, 'Cms.components.widgets.layouts.GenericListDefaultLayout', 'Hudson Nguyen', '1.0', 't', null, null, '4');",
    "INSERT INTO public.".$user_site_prefix."widget_layout (name,description,images,path,author,version,status,creation_datetime,last_update,widget_id) VALUES ('Default', 'Show details of a content item', null, 'Cms.components.widgets.layouts.GenericViewDefaultLayout', 'Hudson Nguyen ', '1.0', 't', null, null, '3');",

    // create table widget_layout_setting
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'widget_layout_setting";',
    'CREATE TABLE "public"."'.$user_site_prefix.'widget_layout_setting" (
        "id" serial NOT NULL,
        "name" varchar(64) NOT NULL,
        "label" varchar(64),
        "value" text NOT NULL,
        "description" text,
        "setting_group" varchar(128),
        "ordering" int4,
        "widget_layout_id" int4 NOT NULL,
        "visible" bool DEFAULT true NOT NULL
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table widget_layout_setting
    'ALTER TABLE public.'.$user_site_prefix.'widget_layout_setting ADD PRIMARY KEY ("id");',
    // insert into table widget_layout_setting
    "INSERT INTO public.".$user_site_prefix."widget_layout_setting (name,label,value,description,setting_group,ordering,widget_layout_id,visible) VALUES ('ShowResultCoutner', 'Show result counter', '1', 'Display the result counter, i.e. Displaying 1 - 5 of 30 results', 'General Settings', '1', '1', 't');",
    "INSERT INTO public.".$user_site_prefix."widget_layout_setting (name,label,value,description,setting_group,ordering,widget_layout_id,visible) VALUES ('PagerStyle', 'Pager button style', '0', 'Type of pagination buttons in case there are more posts to list on one page', 'General Settings', '1', '1', 't');",
    "INSERT INTO public.".$user_site_prefix."widget_layout_setting (name,label,value,description,setting_group,ordering,widget_layout_id,visible) VALUES ('PageSize', 'Page size', '10', 'Number of posts displayed per page', 'General Settings', '0', '1', 't');",

    // create table widget_setting
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'widget_setting";',
    'CREATE TABLE "public"."'.$user_site_prefix.'widget_setting" (
        "id" serial NOT NULL,
        "name" varchar(64) NOT NULL,
        "label" varchar(64),
        "value" text NOT NULL,
        "description" text,
        "setting_group" varchar(128),
        "ordering" int4,
        "widget_id" int4 NOT NULL,
        "visible" bool DEFAULT true NOT NULL
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table widget_setting
    'ALTER TABLE public.'.$user_site_prefix.'widget_setting ADD PRIMARY KEY ("id");',
    
    // create table workflow
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'workflow";',
    'CREATE TABLE "public"."'.$user_site_prefix.'workflow" (
        "id" serial NOT NULL,
        "site_id" int8,
        "name" varchar(100) NOT NULL,
        "url" varchar(100),
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6),
        "type" int2 DEFAULT 0,
        "master_layout_revision" int8
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table workflow
    'ALTER TABLE public.'.$user_site_prefix.'workflow ADD PRIMARY KEY ("id");',
    
    // create table form
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'form";',
    'CREATE TABLE "public"."'.$user_site_prefix.'form" (
        "id" serial NOT NULL,
        "name" varchar(255),
        "description" text,
        "email" varchar(255),
        "redirect" text,
        "success_message" text,
        "captcha" bool,
        "status" bool,
        "creation_datetime" timestamp(6),
        "last_update" timestamp(6),
        "table_name" varchar(255),
        "content_id" int8,
        "log" bool DEFAULT false,
        "permission" text
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table form
    'ALTER TABLE public.'.$user_site_prefix.'form ADD PRIMARY KEY ("id");',
    
    // create table form_element
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'form_element";',
    'CREATE TABLE "public"."'.$user_site_prefix.'form_element" (
        "id" serial NOT NULL,
        "form_id" int8,
        "title" text,
        "guidelines" text,
        "size" varchar(6),
        "is_required" int2,
        "is_unique" int2,
        "is_private" int2,
        "type" varchar(50),
        "position" int4,
        "default_value" text,
        "constraint" varchar(50),
        "total_child" int4 DEFAULT 0,
        "column_name" varchar(255),
        "lookup_type" varchar(50),
        "lookup_value" varchar(50)
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table form_element
    'ALTER TABLE public.'.$user_site_prefix.'form_element ADD PRIMARY KEY ("id");',
    
    // create table form_submission_log
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'form_submission_log";',
    'CREATE TABLE "public"."'.$user_site_prefix.'form_submission_log" (
        "id" serial NOT NULL,
        "form_id" int8,
        "submitted_date" timestamp(6),
        "ip" varchar(32),
        "user_agent" varchar(255),
        "successful" bool DEFAULT false
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table form_submission_log
    'ALTER TABLE public.'.$user_site_prefix.'form_submission_log ADD PRIMARY KEY ("id");',
    
    // create table layout
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'layout";',
    'CREATE TABLE "public"."'.$user_site_prefix.'layout" (
        "id" serial NOT NULL,
        "name" varchar(255) NOT NULL,
        "data" text,
        "width" int4
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table layout
    'ALTER TABLE public.'.$user_site_prefix.'layout ADD PRIMARY KEY ("id");',
    
    // create table layout_container
    'DROP TABLE IF EXISTS "public"."'.$user_site_prefix.'layout_container";',
    'CREATE TABLE "public"."'.$user_site_prefix.'layout_container" (
        "id" serial NOT NULL,
        "container" varchar(255) NOT NULL,
        "layout_id" int8 DEFAULT 0 NOT NULL,
        "page_id" int8 DEFAULT 0 NOT NULL,
        "revision_id" int8 DEFAULT 0 NOT NULL
        )
        WITH (OIDS=FALSE);
    ',
    // add primary key for table layout_container
    'ALTER TABLE public.'.$user_site_prefix.'layout_container ADD PRIMARY KEY ("id");',
);

// insert module Cms
$sqls[] = "INSERT INTO public.".$user_site_prefix."module (name,friendly_name,description,version,has_back_end,ordering,icon,enabled,is_system) VALUES ('Cms', 'Cms', null, '1.0', 'y', '0', null, 't', 't');";
// get each module to insert into table module
if (!empty($this->site->modules)){
    $modules = unserialize($this->site->modules);
    $modules[] = 'Site';
}else{
    $modules = array('Site');
}

if (is_array($modules) && count($modules) > 0)
{
    foreach($modules as $key => $module)
    {
        $moduleModel = CmsModule::model()->find('name=:name',array(':name'=>$module));
        if (is_null($moduleModel)) continue;
        $sqls[] = "INSERT INTO public.".$user_site_prefix."module (name,friendly_name,description,version,has_back_end,ordering,icon,enabled,is_system) VALUES ('".$moduleModel->name."', '".$moduleModel->friendly_name."', '".$moduleModel->description."', '".$moduleModel->version."', 'n', '".($key+1)."', '".$moduleModel->logo."', 't', 'f');";
    }
}    
